package com.bj58.data.emailreports.utils;

import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @author Administrator
 *
 */
public class ExcelUtil {
		/**
		 * @param <T> 通用报表excel下载 & 生成文件
		 * @param excelname  表格名
		 * @param response  不为NULL则直接返回WWB以供下载    为NULL则配合pathName生成EXCEL文件
		 * @param titles	表格标题
		 * @param rsList   数据集
		 * @param clazz
		 * @param pathName  EXCEL文件生成路径  为""则不生成
		 * @return
		 */
		@SuppressWarnings("finally")
		public  static<T> WritableWorkbook  writeExcelCommon(String excelname,HttpServletResponse response,String[]titles,List<T> rsList,Class<T> clazz,String pathName){
	        try {
	        	WritableWorkbook wwb  = null;
	        	if(null != response){
	        		OutputStream os = response.getOutputStream();
	        		wwb = Workbook.createWorkbook(os); 
	        	}else{
	        		wwb = Workbook.createWorkbook(new File(pathName));
	        	}
		        
		        Map<Integer,List<T>> rsMap = new HashMap<Integer,List<T>>();
		        int sheetMaxNum = 20000;
		        if(rsList.size() > sheetMaxNum){
		        	int sheetCount = rsList.size() / sheetMaxNum;
		        	if(rsList.size() % sheetMaxNum > 0){
		        		sheetCount ++;
		        	}
		        	for(int i = 0 ;i < sheetCount; i++){
		        		int beginIndex = i*sheetMaxNum;
		        		int endIndex = (i+1)*sheetMaxNum;
		        		if(endIndex > rsList.size()){
		        			endIndex = rsList.size();
		        		}
		        		rsMap.put(i, rsList.subList(beginIndex, endIndex));
		        	}
		        }else{
		        	rsMap.put(0, rsList);
		        }
		        Iterator<Integer> rsKey = rsMap.keySet().iterator();
		        
		        //WritableCellFormat  数字格式问题处理
		        List<WritableCellFormat> wcfList = getWCFList();
		        
		        while(rsKey.hasNext()){
		        	int sheetNum = rsKey.next();
		        	List<T> subrsList = rsMap.get(sheetNum);
		        	String sheetName = "";
		        	if(sheetNum == 0){
		        		sheetName = excelname;
		        	}else{
		        		sheetName = excelname+"_"+sheetNum;
		        	}
		        	WritableSheet ws = wwb.createSheet(sheetName,sheetNum); 
		        	 //写列标
			        Label label = new Label(0,0,titles[0]);
			        ws.addCell(label); 
			        for(int i=1;i<titles.length;i++){
			        	  label = new Label(i,0,titles[i]); 
			        	  ws.addCell(label); 
			        }
			        //写内容
			        Method[] mm = clazz.getMethods();
			        Map<String,Method> methodMap = new HashMap<String,Method>();
					for(Method m: mm){
						methodMap.put(m.getName(), m);
					}
					
			        Field[] fields = clazz.getDeclaredFields();
			        for(int i =0;i<subrsList.size();i++){
			        	T vo = subrsList.get(i);
			        	int ind = 0; 
			        	for(int j = 0; j< fields.length;j++){
		        			Class<?> filedCls = fields[j].getType();//字段类型
		        			int isRate = 0;//是否为百分比  默认不是
		        			
			        		String value = "";
			        		String methodName = "get" + fields[j].getName().substring(0,1).toUpperCase()+ fields[j].getName().substring(1);
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.ItemMetrics")){ //新团购项目报表下载需要中间跳过几个字段项
			        			if(methodName.equals("getStat_date") || methodName.equals("getBusiness_license") ){
			        				continue;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.CompanyMetricsHour")){ //新分公司报表下载需要中间跳过几个字段项
			        			if(methodName.equals("getStat_date") || methodName.equals("getStat_hour")
			        					||  methodName.equals("getConsume_commission") || methodName.equals("getSerialVersionUID")){
			        				continue;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.AdCityMetricsHour")
			        				|| clazz.getName().equals("com.bj58.groupbuy.action.model.AdCityMetricsDay")){ //广告渠道下载 从city_name字段后开始忽略
			        			
			        			if(methodName.equals("getCity_name")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.KeywordMetrics")){ //搜索关键词 
			        			String jude = titles[0];
			        			if("日期".equals(jude)){
			        				if(methodName.equals("getKeyword")){
			        					continue;
			        				}
			        				if(methodName.equals("getStat_hour")){
				        				break;
				        			}
			        			}else if("关键词".equals(jude)){
			        				if(methodName.equals("getStat_date")){
			        					continue;
			        				}
			        				if(methodName.equals("getExit_count")){
				        				break;
				        			}
			        			}
			        			
			        		}
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.SHOrderStat")){ //二手订单统计从order_count字段后开始忽略
			        			if(methodName.equals("getOrder_count")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.FinanceCheckIn")){ 
			        			if(methodName.equals("getCreate_time")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.ADClickStat")){ 
			        			if(methodName.equals("getEnt_id")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.FinanceCheckOut")){
			        			if(methodName.equals("getPmcid")){
			        				value = methodMap.get("getWork_id").invoke(vo).toString()+"-"+methodMap.get("getWork_type").invoke(vo).toString();
			        				label = new Label(ind,i+1,value); 
					        		ws.addCell(label); 
					        		ind ++;
			        				continue;
			        			}
			        			if(methodName.equals("getWork_id")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.ADClickStat")){
			        			if(methodName.equals("getShop_type")){
			        				value = methodMap.get(methodName).invoke(vo).toString();
			        				if("0".equals(value)){
			        					value = "免费";
			        				}else if("1".equals(value)){
			        					value = "付费";
			        				}
			        				label = new Label(ind,i+1,value); 
					        		ws.addCell(label); 
					        		ind ++;
			        				continue;
			        			}
			        			if(methodName.equals("getEnt_id")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.CSTaskStats")){
			        			if(methodName.equals("getDone_rate")){
			        				DecimalFormat df = new DecimalFormat("0.00");
			        				value =df.format(Double.valueOf(methodMap.get(methodName).invoke(vo).toString())*100)+"%";
			        				label = new Label(ind,i+1,value); 
					        		ws.addCell(label); 
					        		ind ++;
			        				continue;
			        			}
			        		}
			        	
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.CSFirsttaskStats")){
			        			if(methodName.contains("rate")){
			        				DecimalFormat df = new DecimalFormat("0.00");
			        				value =df.format(Double.valueOf(methodMap.get(methodName).invoke(vo).toString())*100)+"%";
			        				label = new Label(ind,i+1,value); 
					        		ws.addCell(label); 
					        		ind ++;
			        				continue;
			        			}
			        			if(methodName.equals("getCs_userId")){
			        				break;
			        			}
			        		}

			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.CSSecondtaskStats")){
			        			if(methodName.contains("rate")){
			        				DecimalFormat df = new DecimalFormat("0.00");
			        				value =df.format(Double.valueOf(methodMap.get(methodName).invoke(vo).toString())*100)+"%";
			        				label = new Label(ind,i+1,value); 
					        		ws.addCell(label); 
					        		ind ++;
			        				continue;
			        			}
			        			if(methodName.equals("getDone_byfirst")){
			        				break;
			        			}
			        		}
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.SalerMetrics")){
			        			if(methodName.equals("getGroupbuy_id") || methodName.equals("getItem_name") || methodName.equals("getIs_new_partner")){
			        				if(null == methodMap.get(methodName).invoke(vo)){
			        					continue;
			        				}else if (methodMap.get(methodName).invoke(vo).toString().equals("0")){
			        					continue;
			        				}
			        			}
			        		}
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.GBReportGBCate")){
			        			if(null == methodMap.get(methodName).invoke(vo)){
									value ="";
			        			}else{
			        				DecimalFormat df = new DecimalFormat("0.00");
			        				String temp = methodMap.get(methodName).invoke(vo).toString();
				        			if (temp.contains(".") && !temp.contains("%")){
				        				value =df.format(Double.valueOf(temp));
				        				if(methodName.equals("getConversion_rate")){
				        					value = value+"%";
				        				}
				        			}else{
				        				value = temp;
				        			}
			        			}
			        			label = new Label(ind,i+1,value); 
					       		ws.addCell(label); 
					       		ind ++;
			        			continue;
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.ShopKpiDay") || clazz.getName().equals("com.bj58.groupbuy.action.model.ShopKpiHour")){
			        			if(methodName.equals("getNewpvratio")){
			        				break;
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.ShopProductStats")){
			        			if(titles.length == 28){
			        				if(methodName.equals("getPid") || methodName.equals("getTitle")){
			        					continue;
			        				}
			        			}else if(titles.length == 29){
			        				if(methodName.equals("getDay")){
			        					continue;
			        				}
			        			}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.PrecisionDataAll") ){
			        			try {
			        				if(null == methodMap.get(methodName).invoke(vo)){
										value ="";
			        				}else{
				        				value = methodMap.get(methodName).invoke(vo).toString();
				        				//百分比
					        			if(methodName.equals("getCTR_Channel")||methodName.equals("getCTR_AD")
					        					||methodName.equals("getPVR")||methodName.equals("getMember_cost_zb")
					        					||methodName.equals("getMember_cus_zb")){
					        				isRate  = 1;
					        			}
			        				}
			        				addExcelValue(filedCls, ws, value, isRate , ind, i+1,wcfList);
								} catch (Exception e) {
									e.printStackTrace();
								}finally{
									ind ++;
		        					continue;
								}
			        		}
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.PrecisionDataDetail")
			        				|| clazz.getName().equals("com.bj58.groupbuy.action.model.PrecisionDataPFCDetail")){
			        			try {
			        				if(null == methodMap.get(methodName).invoke(vo)){
										value ="";
			        				}else{
			        					value = methodMap.get(methodName).invoke(vo).toString();
			        					if(methodName.equals("getState")){
			        						if("0".equals(value)){
												value = "成功";
											}else if("1".equals(value)){
												value = "初始化参与拍卖";
											}else if("2".equals(value)){
												value = "删除状态";
											}else if("3".equals(value)){
												value = "出价过低";
											}else if("4".equals(value)){
												value = "余额不足";
											}else if("5".equals(value)){
												value = "推广未通过审核";
											}else if("6".equals(value)){
												value = "用户被封禁";
											}else if("7".equals(value)){
												value = "频道被删除";
											}
			        					}
			        				}
			        				addExcelValue(filedCls, ws, value, isRate , ind, i+1,wcfList);
								}catch (Exception e) {
									e.printStackTrace();
								}finally{
									ind ++;
					        		continue;
								}
			        			
			        		}
			        		
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.PrecisionDataYEBD")){
			        			try {
			        				if(methodName.equals("getYh_cz")){
			        					System.out.println("111");
			        				}
			        				if(null == methodMap.get(methodName).invoke(vo)){
										value ="";
			        				}else{
			        					value = methodMap.get(methodName).invoke(vo).toString();
			        					if(methodName.equals("getJz_rate")){
					        				isRate = 1;
					        			}
			        				}
			        				addExcelValue(filedCls, ws, value, isRate , ind, i+1,wcfList);
								} catch (Exception e) {
									e.printStackTrace();
								}finally{
									ind ++;
					        		continue;
								}
			        		}
			        		
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.PrecisionDataPCDetail") 
			        				|| clazz.getName().equals("com.bj58.groupbuy.action.model.PrecisionDataCity") ){
			        			try {
			        				if(null == methodMap.get(methodName).invoke(vo)){
										value ="";
									}else{
										value = methodMap.get(methodName).invoke(vo).toString();
									}
			        				addExcelValue(filedCls, ws, value, isRate , ind, i+1,wcfList);
								} catch (Exception e) {
									e.printStackTrace();
								}finally{
									ind ++;
					        		continue;
								}
//			        			if(methodName.contains("amount")){
//			        				if(null == methodMap.get(methodName).invoke(vo)){
//										value ="";
//									}else{
//										DecimalFormat df = new DecimalFormat("0.0");
//										value = df.format(Double.valueOf(methodMap.get(methodName).invoke(vo).toString()));
//									}
//			        				label = new Label(ind,i+1,value); 
//					        		ws.addCell(label); 
//					        		ind ++;
//					        		continue;
//			        			}
			        		}
			        		
			        		
			        		if(clazz.getName().equals("com.bj58.groupbuy.action.model.ShopCheckStat")){
			        			if(methodName.contains("Rate")){
			        				if(null == methodMap.get(methodName).invoke(vo)){
										value ="";
									}else{
										DecimalFormat df = new DecimalFormat("0.00");
										value = df.format(Double.valueOf(methodMap.get(methodName).invoke(vo).toString())*100)+"%";
									}
			        				label = new Label(ind,i+1,value); 
					        		ws.addCell(label); 
					        		ind ++;
					        		continue;
			        			}
			        		}
			        		
							if(methodMap.containsKey(methodName)){
								if(null == methodMap.get(methodName).invoke(vo)){
									value ="";
								}else{
									value = methodMap.get(methodName).invoke(vo).toString();
								}
							}
			        		label = new Label(ind,i+1,value); 
			        		ws.addCell(label); 
			        		ind ++;
			        	}
		        	}
		        }
		        return wwb;
			} catch (Exception e) {
				e.printStackTrace();
			}
		   return null;
	    }
		
		
		/** Excel值写入
		 * @param filedCls  字段类别
		 * @param ws    Excel表写入对象
		 * @param value  值
		 * @param isRate  是否为百分比  0 : 否  1：是
		 * @param col  
		 * @param row 
		 * @return
		 * @throws Exception
		 */
		private static boolean addExcelValue(Class<?> filedCls,WritableSheet ws,String value,int isRate,int col,int row,List<WritableCellFormat> wcfList)throws Exception{
			WritableCellFormat wcf = null;
			try{
				if(isRate == 1){
					wcf = wcfList.get(0);
					Number doubleNum = new Number(col,row,Double.valueOf(value)/100,wcf);
					ws.addCell(doubleNum); 
				}else{
					if(filedCls == Integer.class || filedCls == int.class){//整型短数字   
						wcf = wcfList.get(1);
						Number doubleNum = new Number(col,row,Double.valueOf(value),wcf);
						ws.addCell(doubleNum); 
					}else if(filedCls == Double.class || filedCls == double.class ){//小数点
						wcf = wcfList.get(2);
						Number doubleNum = new Number(col,row,Double.valueOf(value),wcf);
						ws.addCell(doubleNum); 
					}else if(filedCls == String.class ){ 
						Label label = new Label(col,row,value); 
		        		ws.addCell(label); 
					}else if(filedCls == Long.class  || filedCls == long.class){ //整型长数字   
						if(value.length() > 15 ){//超过15位则用字符格式
							Label label = new Label(col,row,value); 
			        		ws.addCell(label); 
						}else{
							wcf = wcfList.get(3);
							Number doubleNum = new Number(col,row,Double.valueOf(value),wcf);
							ws.addCell(doubleNum); 
						}
					}
				}
				return true;
			}catch (Exception e) {
				return false;
			}
		}
		
		private static List<WritableCellFormat> getWCFList(){
			List<WritableCellFormat>  wcfList = new ArrayList<WritableCellFormat>();
			
			WritableCellFormat format1 = null; //百分比格式
        	NumberFormat nf1 = new NumberFormat("0.00%");   
        	format1 = new WritableCellFormat(nf1);
        	wcfList.add(format1);
        	
			WritableCellFormat format2 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER); //整型千分位格式
        	wcfList.add(format2);
        	
			WritableCellFormat format3 = null;; //浮点2位小数
            NumberFormat nf3 = new NumberFormat("0.00");   
            format3 = new WritableCellFormat(nf3);
        	wcfList.add(format3);
			
    		WritableCellFormat format4 = new WritableCellFormat(NumberFormats.INTEGER); //整型长数字 (不超过15)
        	wcfList.add(format4);
        	
			return wcfList;
		}
}
